package com.mzj.saas.mservice.propertybill.service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.util.StrUtil;
import com.haixiaoke.saas.repository.pojo.UserFloatingPopulationRecord;
import com.haixiaoke.saas.repository.service.IUserFloatingPopulationRecordService;
import com.mzj.saas.commons.ApiData;
import com.mzj.saas.commons.CustomException;
import com.mzj.saas.commons.ResultBean;
import com.mzj.saas.commons.StatusCode;
import com.mzj.saas.commons.enums.BacklogItemTypeEnum;
import com.mzj.saas.commons.enums.RemainingStatusEnum;
import com.mzj.saas.commons.vo.redis.TokenRedisVo;
import com.mzj.saas.config.sms.SmsConfig;
import com.mzj.saas.mservice.backlog.repository.BacklogItemHouseOtherBillRepository;
import com.mzj.saas.mservice.backlog.repository.BacklogItemLockRepository;
import com.mzj.saas.mservice.backlog.repository.BacklogItemPropertyOtherBillRepository;
import com.mzj.saas.mservice.backlog.repository.BacklogItemRepository;
import com.mzj.saas.mservice.bill.entity.ExpenseBill;
import com.mzj.saas.mservice.bill.entity.ExpenseBillRelation;
import com.mzj.saas.mservice.bill.entity.HouseExpenseRuleRef;
import com.mzj.saas.mservice.bill.entity.HouseWaitPaymentDetail;
import com.mzj.saas.mservice.bill.repository.ExpenseBillRelationRepository;
import com.mzj.saas.mservice.bill.repository.ExpenseBillRepository;
import com.mzj.saas.mservice.bill.repository.HouseExpenseRuleRefRepository;
import com.mzj.saas.mservice.bill.repository.HouseWaitPaymentDetailRepository;
import com.mzj.saas.mservice.bill.vo.SaveExpenseBillVOAndSurrenderVO;
import com.mzj.saas.mservice.community.entity.Community;
import com.mzj.saas.mservice.community.entity.CommunityBuild;
import com.mzj.saas.mservice.community.entity.HouseSurrender;
import com.mzj.saas.mservice.community.repository.CommunityBuildRepository;
import com.mzj.saas.mservice.community.repository.CommunityRepository;
import com.mzj.saas.mservice.community.repository.HouseSurrenderRepository;
import com.mzj.saas.mservice.community.vo.HouseSurrenderVO;
import com.mzj.saas.mservice.contract.repository.ContractRepository;
import com.mzj.saas.mservice.department.entity.Department;
import com.mzj.saas.mservice.department.repository.DepartmentRepository;
import com.mzj.saas.mservice.eligible.entity.DepartmentEnterpriseTagRef;
import com.mzj.saas.mservice.eligible.entity.DepartmentHierarchy;
import com.mzj.saas.mservice.eligible.repository.DepartmentEnterpriseTagRefRepository;
import com.mzj.saas.mservice.eligible.repository.DepartmentHierarchyRepository;
import com.mzj.saas.mservice.excel.service.ExcelService;
import com.mzj.saas.mservice.excel.vo.ExcelTemplateVo;
import com.mzj.saas.mservice.house.entity.House;
import com.mzj.saas.mservice.house.entity.HouseMatchUser;
import com.mzj.saas.mservice.house.repository.HouseMatchUserRepository;
import com.mzj.saas.mservice.house.repository.HouseRepository;
import com.mzj.saas.mservice.house.vo.HouseVO;
import com.mzj.saas.mservice.lock.Repository.HouseRemainingPostponeRepository;
import com.mzj.saas.mservice.lock.Repository.SmartLockRepository;
import com.mzj.saas.mservice.lock.entity.HouseRemainingPostpone;
import com.mzj.saas.mservice.lock.entity.SmartLock;
import com.mzj.saas.mservice.lock.service.LockAppService;
import com.mzj.saas.mservice.lockManager.service.LockManagerService;
import com.mzj.saas.mservice.property.entity.*;
import com.mzj.saas.mservice.property.repository.*;
import com.mzj.saas.mservice.property.service.CommunityHouseUserService;
import com.mzj.saas.mservice.property.vo.BillListVo;
import com.mzj.saas.mservice.property.vo.MatchUserHouseVO;
import com.mzj.saas.mservice.propertybill.entity.EstateViolateBill;
import com.mzj.saas.mservice.propertybill.entity.HouseReturnBillRelation;
import com.mzj.saas.mservice.propertybill.entity.HouseReturnPremium;
import com.mzj.saas.mservice.propertybill.repository.EstateExpenseBillDetailRepository;
import com.mzj.saas.mservice.propertybill.repository.EstateViolateBillRepository;
import com.mzj.saas.mservice.propertybill.repository.HouseReturnBillRelationRepository;
import com.mzj.saas.mservice.propertybill.repository.HouseReturnPremiumRepository;
import com.mzj.saas.mservice.propertybill.vo.LoadBillVO;
import com.mzj.saas.mservice.sms.service.SmsService;
import com.mzj.saas.mservice.sys.entity.AppUser;
import com.mzj.saas.mservice.sys.repository.AppUserRepository;
import com.mzj.saas.mservice.sys.utils.DataUtils;
import com.mzj.saas.mservice.sys.utils.SqlUtils;
import com.mzj.saas.redis.RedisService;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;

import static com.mzj.saas.commons.util.ConvertUtils.fen2yuan;
import static com.mzj.saas.commons.util.ConvertUtils.yuan2fen;

@Service
public class WithDrawService {
    private static final Logger log = LoggerFactory.getLogger(PayEstateBillService.class);

    @Value("${contract.tmp.dir}")
    private String contractTmpDir;
    @Autowired
    private RedisService redisService;
    @Autowired
    private SmsService smsService;
    @Autowired
    private SmsConfig smsConfig;
    @Autowired
    private DepartmentHierarchyRepository departmentHierarchyRepository;
    @Autowired
    private DepartmentEnterpriseTagRefRepository departmentEnterpriseTagRefRepository;
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private DepartmentRepository departmentRepository;
    @Autowired
    private CommunityBuildRepository communityBuildRepository;
    @Autowired
    private CommunityRepository communityRepository;
    @Autowired
    private HouseSurrenderRepository houseSurrenderRepository;
    @Autowired
    private HouseRepository houseRepository;
    @Autowired
    private ExpenseBillRepository expenseBillRepository;
    @Autowired
    private EstateExpenseBillRepository estateExpenseBillRepository;
    @Autowired
    private EstateRentBillRepository estateRentBillRepository;
    @Autowired
    private ExpenseBillRelationRepository expenseBillRelationRepository;
    @Autowired
    private HouseWaitPaymentDetailRepository houseWaitPaymentDetailRepository;
    @Autowired
    private CommunityHouseUserService communityHouseUserService;
    @Autowired
    private LockManagerService lockManagerService;
    @Autowired
    private BacklogItemRepository backlogItemRepository;
    @Autowired
    private BacklogItemLockRepository backlogItemLockRepository;
    @Autowired
    private CommunityHouseUserRepository communityHouseUserRepository;
    @Autowired
    private HouseMatchUserRepository houseMatchUserRepository;
    @Autowired
    private ContractRepository contractRepository;
    @Autowired
    private MatchUserHouseRepository matchUserHouseRepository;
    @Autowired
    private LockAppService lockAppService;
    @Autowired
    private AppUserRepository appUserRepository;
    @Autowired
    private ExcelService excelService;
    @Autowired
    private SmartLockRepository smartLockRepository;
    @Autowired
    private EstateExpenseBillDetailRepository estateExpenseBillDetailRepository;
    @Autowired
    private BacklogItemPropertyOtherBillRepository backlogItemPropertyOtherBillRepository;
    @Autowired
    private BacklogItemHouseOtherBillRepository backlogItemHouseOtherBillRepository;
    @Autowired
    private HouseRemainingRepository houseRemainingRepository;
    @Autowired
    private HouseRemainingRecordRepository houseRemainingRecordRepository;
    @Autowired
    private HouseRemainingRelationRepository houseRemainingRelationRepository;
    @Autowired
    private HouseReturnBillRelationRepository houseReturnBillRelationRepository;
    @Autowired
    private HouseReturnPremiumRepository houseReturnPremiumRepository;
    @Autowired
    private MatchUserRepository matchUserRepository;
    @Autowired
    private DepartmentRemainingRepository departmentRemainingRepository;
    @Autowired
    private DepartmentRemainingRefRepository departmentRemainingRefRepository;
    @Autowired
    private DepartmentRemainingRecordRepository departmentRemainingRecordRepository;
    @Autowired
    private HouseRemainingPostponeRepository houseRemainingPostponeRepository;
    @Autowired
    private EstateViolateBillRepository estateViolateBillRepository;
    @Autowired
    private IUserFloatingPopulationRecordService userFloatingPopulationRecordService;
    @Autowired
    private HouseExpenseRuleRefRepository houseExpenseRuleRefRepository;

    /**
     * 退租管理列表
     *
     * @param accessToken
     * @param pageNumber
     * @param pageSize
     * @param vo
     * @param houseIdList 查询当前机构名下退租管理列表
     */
    public ResultBean<ApiData<HouseVO>> list(String accessToken, Integer pageNumber, Integer pageSize, HouseVO vo,
                                             List<String> houseIdList) {
        try {
            // 登录校验
            TokenRedisVo tokenRedisVo = redisService.findTokenVo(accessToken);
            if (tokenRedisVo == null) {
                return ResultBean.failedResultOfToken();
            }
            Long enterpriseId = tokenRedisVo.getEnterpriseId();
            DepartmentHierarchy hierarchy = departmentHierarchyRepository.findByOrgIdAndStatus(enterpriseId, 1);
            if (hierarchy == null) {
                return ResultBean.failedResultWithMsg("用户所属机构未设置层级架构");
            }
            Long id = hierarchy.getId();
            List<DepartmentEnterpriseTagRef> refs = departmentEnterpriseTagRefRepository.findByDepRefId(id);
            List<Long> enterpriseTagIds = refs.stream().map(DepartmentEnterpriseTagRef::getTagId).distinct()
                    .collect(Collectors.toList());// 用户企业标签

            // 1、政府机构，根据区域标签所对应区域id查
            Long areaTagId = hierarchy.getTagId();// 区域标签
            Long areaId = null;
            if (enterpriseTagIds.contains(3L)) {
                areaId = hierarchy.getAreaId();// 区域id
            }
            String areaTag = "province_id";
            if (areaTagId == 1) {
                areaTag = "province_id";
            } else if (areaTagId == 2) {
                areaTag = "city_id";
            } else if (areaTagId == 3) {
                areaTag = "district_id";
            } else if (areaTagId == 4) {
                areaTag = "street_id";
            } else if (areaTagId == 5) {
                areaTag = "community_id";
            }

            Long hierarchyOrgId = null;
            if (enterpriseTagIds.contains(2L)) {
                hierarchyOrgId = hierarchy.getOrgId();
            }
            Long unitLesseeId = null;
            if (enterpriseTagIds.contains(7L)) {
                unitLesseeId = hierarchy.getOrgId();
            }
            // 3、物业，查已绑定该物业的小区下的所有房产
            List<String> residenceCommunityIds = new ArrayList<>();
            if (enterpriseTagIds.contains(4L)) {
                List<Community> communities = communityRepository.findByOrgIdAndStatus(enterpriseId, 1);
                residenceCommunityIds = communities.stream().map(Community::getId).distinct()
                        .collect(Collectors.toList());// 小区id
            }

            StringBuilder where = new StringBuilder();
            List<Object> args = new ArrayList<>();
            where.append(" where 1 = 1");
            where.append(" and (h." + areaTag + " = ? or h.org_id = ? or h.unit_lessee_id = ? or h.residence_community_id in ");
            args.add(areaId);
            args.add(hierarchyOrgId);
            args.add(unitLesseeId);
            if (residenceCommunityIds != null && !residenceCommunityIds.isEmpty()) {
                where.append(SqlUtils.foreachIn(residenceCommunityIds.size()) + ")");
                args.addAll(residenceCommunityIds);
            } else {
                where.append("(null))");
            }
            where.append("  AND c.status = 1 ");

            // 查询条件
            if (vo != null) {
                // 房屋ID
                if (StringUtils.isNotBlank(vo.getId())) {
                    where.append(" and h.id = ?");
                    args.add(vo.getId());
                }
                // 房屋ID集合
                if (houseIdList != null && !houseIdList.isEmpty()) {
                    where.append(" AND h.id in ").append(SqlUtils.foreachIn(houseIdList.size()));
                    args.addAll(houseIdList);
                }
                // 小区id
                if (StringUtils.isNotBlank(vo.getResidenceCommunityId())) {
                    where.append(" and h.residence_community_id = ?");
                    args.add(vo.getResidenceCommunityId());
                }
                // 楼宇id
                if (vo.getBuildingNum() != null) {
                    where.append(" and h.building_num = ?");
                    args.add(vo.getBuildingNum());
                }
                // 单元id
                if (vo.getUnit() != null) {
                    where.append(" and h.unit = ?");
                    args.add(vo.getUnit());
                }
                // 房号
                if (StringUtils.isNotBlank(vo.getRoomNumber())) {
                    where.append(" and h.room_number = ?");
                    args.add(vo.getRoomNumber());
                }
                // 小区名称
                if (StringUtils.isNotBlank(vo.getResidenceCommunityName())) {
                    where.append(" and c.name like ?");
                    args.add("%" + vo.getResidenceCommunityName() + "%");
                }

                // 楼层
                if (vo.getFloor() != null) {
                    where.append(" and h.floor = ?");
                    args.add(vo.getFloor());
                }
                // 面积(平方米)
                if (vo.getArea() != null) {
                    where.append(" and h.area = ?");
                    args.add(vo.getArea());
                }
                // 省
                if (vo.getProvinceId() != null) {
                    where.append(" and h.province_id = ?");
                    args.add(vo.getProvinceId());
                }
                // 市
                if (vo.getCityId() != null) {
                    where.append(" and h.city_id = ?");
                    args.add(vo.getCityId());
                }
                // 区
                if (vo.getDistrictId() != null) {
                    where.append(" and h.district_id = ?");
                    args.add(vo.getDistrictId());
                }
                // 街道
                if (vo.getStreetId() != null) {
                    where.append(" and h.street_id = ?");
                    args.add(vo.getStreetId());
                }
                // 社区
                if (vo.getCommunityId() != null) {
                    where.append(" and h.community_id = ?");
                    args.add(vo.getCommunityId());
                }
                // 小区地址
                if (StringUtils.isNotBlank(vo.getAddress())) {
                    where.append(" and h.address like ?");
                    args.add("%" + vo.getAddress() + "%");
                }
                // 所属物业
                if (vo.getHousePropertyId() != null) {
                    where.append(" and h.house_property_id = ?");
                    args.add(vo.getHousePropertyId());
                }
                // 产权所有人、产权单位
                if (StringUtils.isNotBlank(vo.getOwnerName())) {
                    where.append(" and (h.owner_name like ? or d.name like ?)");
                    args.add("%" + vo.getOwnerName() + "%");
                    args.add("%" + vo.getOwnerName() + "%");
                }
                // 户型室
                if (vo.getRoom() != null) {
                    where.append(" and h.room = ?");
                    args.add(vo.getRoom());
                }
                // 户型厅
                if (vo.getHall() != null) {
                    where.append(" and h.hall = ?");
                    args.add(vo.getHall());
                }
                // 户型卫
                if (vo.getToilet() != null) {
                    where.append(" and h.toilet = ?");
                    args.add(vo.getToilet());
                }

                // 性质
                if (vo.getHouseNature() != null) {
                    where.append(" and h.house_nature = ?");
                    args.add(vo.getHouseNature());
                }

                // 退租状态 退租状态 0无 1已申请 2已验房 3未结清 4已结清 6已完成
                if (vo.getWithDraw() != null) {
                    where.append(" and m.status = ?");
                    args.add(vo.getWithDraw());
                }

                if (StringUtils.isNotBlank(vo.getSearch())) {
                    where.append(" and (c.name like ? or b.name like ? or u.name like ? or h.room_number like ? )  "); // 搜索关键字字段【小区、楼栋、单元、房号】
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                    args.add("%" + vo.getSearch() + "%");
                }
            }


            String SQL = "SELECT h.*,h.org_id orgId,d.name orgName,c.name residenceCommunityName,b.name buildingNumName,b.type buildType,b.house_number houseNumber,u.name unitName  "
                    + "   ,a1.name province,a2.name city,a3.name district,a4.name street,a5.name community,m.status AS withDraw,m.id AS withDrawId"
                    + "   FROM m_house_surrender m"
                    + "   LEFT JOIN saas_house h ON m.house_id = h.id"
                    + "   LEFT JOIN saas_community c ON c.id=h.residence_community_id  "
                    + "   LEFT JOIN saas_community_build b ON b.id=h.building_num  "
                    + "   LEFT JOIN saas_community_build_unit u ON u.id=h.unit  "
                    + "   LEFT JOIN sys_area a1 ON h.province_id=a1.id  "
                    + "   LEFT JOIN sys_area a2 ON h.city_id=a2.id  "
                    + "   LEFT JOIN sys_area a3 ON h.district_id=a3.id  "
                    + "   LEFT JOIN sys_area a4 ON h.street_id=a4.id  "
                    + "   LEFT JOIN sys_area a5 ON h.community_id=a5.id "
                    + "   LEFT JOIN saas_department d ON h.org_id = d.id";


            String count = "SELECT count(1)  " + "   FROM m_house_surrender m"
                    + "   LEFT JOIN saas_house h ON m.house_id = h.id"
                    + "   LEFT JOIN saas_community c ON c.id=h.residence_community_id  "
                    + "   LEFT JOIN saas_community_build b ON b.id=h.building_num  "
                    + "   LEFT JOIN saas_community_build_unit u ON u.id=h.unit  "
                    + "   LEFT JOIN sys_area a1 ON h.province_id=a1.id  "
                    + "   LEFT JOIN sys_area a2 ON h.city_id=a2.id  "
                    + "   LEFT JOIN sys_area a3 ON h.district_id=a3.id  "
                    + "   LEFT JOIN sys_area a4 ON h.street_id=a4.id  "
                    + "   LEFT JOIN sys_area a5 ON h.community_id=a5.id "
                    + "   LEFT JOIN saas_department d ON h.org_id = d.id";

            ApiData<HouseVO> apiData = new ApiData<>();

            // 退租管理列表
            Long total = jdbcTemplate.queryForObject(count + where, Long.class, args.toArray());
            if (total == 0) {
                return ResultBean.successfulResult(apiData);
            }

            // 查询分页数据
            String offsets = " limit ? offset ?";
            where.append(" ORDER BY m.operator_time desc ").append(" \n").append(offsets);

            args.add(pageSize);
            args.add(pageNumber * pageSize);

            // 退租总数据
            List<HouseVO> data = jdbcTemplate.query(SQL + where, new BeanPropertyRowMapper<>(HouseVO.class),
                    args.toArray());

            // 房产所属物业列表
            List<Long> housePropertyIdList = data.stream().map(HouseVO::getHousePropertyId)
                    .collect(Collectors.toList());
            // 物业对应机构层级
            List<DepartmentHierarchy> departmentHierarchyList = departmentHierarchyRepository
                    .findByOrgIdIn(housePropertyIdList);
            for (HouseVO houseVO : data) {
                for (DepartmentHierarchy departmentHierarchy : departmentHierarchyList) {
                    if (houseVO.getHousePropertyId().equals(departmentHierarchy.getOrgId())) {
                        houseVO.setHouseProperty(departmentHierarchy.getName());
                    }
                }
            }

            // 公租房查询产权单位名称
            List<Long> ownerIds = data.stream().filter(houseVO -> houseVO.getOrgId() != null).map(HouseVO::getOrgId)
                    .distinct().collect(Collectors.toList());
            if (!ownerIds.isEmpty()) {
                // 产权单位
                Map<Long, String> map = departmentRepository.findByIdIn(ownerIds).stream()
                        .collect(Collectors.toMap(Department::getId, Department::getName));
                if (!map.isEmpty()) {
                    // 设置产权所有人、产权单位
                    data.forEach(houseVO -> {
                        if (houseVO.getHouseNature() != null && houseVO.getHouseNature() == 1) {
                            houseVO.setOwnerName(map.get(houseVO.getOrgId()));
                            houseVO.setOrgName(map.get(houseVO.getOrgId()));
                        }
                    });
                }
            }

            // 查询楼栋名称
            List<Long> buildingIds = data.stream().filter(houseVO -> houseVO.getBuildingNum() != null)
                    .map(HouseVO::getBuildingNum).distinct().collect(Collectors.toList());
            if (!buildingIds.isEmpty()) {
                // 楼栋层数
                Map<Long, Integer> map2 = communityBuildRepository.findByIdIn(buildingIds).stream()
                        .filter(houseVO -> houseVO.getHouseNumber() != null)
                        .collect(Collectors.toMap(CommunityBuild::getId, CommunityBuild::getHouseNumber));
                // 楼栋类型
                Map<Long, Integer> map3 = communityBuildRepository.findByIdIn(buildingIds).stream()
                        .filter(houseVO -> houseVO.getType() != null)
                        .collect(Collectors.toMap(CommunityBuild::getId, CommunityBuild::getType));
                if (!map2.isEmpty()) {
                    data.forEach(houseVO -> houseVO.setHouseNumber(map2.get(houseVO.getBuildingNum())));
                }
                if (!map3.isEmpty()) {
                    data.forEach(houseVO -> houseVO.setBuildType(map3.get(houseVO.getBuildingNum())));
                }
            }
            apiData.setTotal(total);
            apiData.setData(data);
            return ResultBean.successfulResult(apiData);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 验房接口 传退租id，房屋id，房屋状态，损坏原因 退租状态：0无 1已申请 2已验房 3未结清 4已结清 6已完成
     *
     * @param accessToken
     * @param
     * @return 申请验房后将合同金额缓存起来 并修改退租状态
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultBean<ApiData<Object>> homeInspection(String accessToken, HouseSurrenderVO vo) {
        try {
            TokenRedisVo tokenVo = redisService.findTokenVo(accessToken);

            if (tokenVo == null) {
                return ResultBean.failedResultOfToken();
            }
            if (vo.getId() == null) {
                return ResultBean.failedResultWithMsg("退租id不能为空");
            }
            if (StringUtils.isBlank(vo.getHouseId())) {
                return ResultBean.failedResultWithMsg("房屋id不能为空");
            }
            if (vo.getHouseStatus() == null) {
                return ResultBean.failedResultWithMsg("房屋状态不能为空");
            }
            if (vo.getHouseStatus() == 2) {
                if (StringUtils.isBlank(vo.getDamageReason())) {
                    return ResultBean.failedResultWithMsg("损坏原因不能为空");
                }
            }
            // 缓存合同信息
            ResultBean<ApiData<HouseSurrenderVO>> resultBean = checkTheContract(accessToken, vo.getId(), vo.getHouseId());
            if (resultBean.getCode().equals(StatusCode.SUCCESS_CODE_10000.getErrorCode())
                    && resultBean.getResultData() != null) {
                List<HouseSurrenderVO> list = resultBean.getResultData().getData();
                HouseSurrenderVO vvo = list.get(0);
                String matchUserName = vvo.getMatchUserName();
                String matchUserPhone = vvo.getMatchUserPhone();
                // 获取承租人信息
                List<MatchUserHouse> byHouseId = matchUserHouseRepository.findByHouseId(vo.getHouseId());
                if (!byHouseId.isEmpty()) {
                    Long matchUserStockId = Long.valueOf(byHouseId.get(0).getMatchUserStockId());
                    Optional<MatchUser> byId = matchUserRepository.findById(matchUserStockId);
                    if (byId.isPresent()) {
                        matchUserName = byId.get().getName();
                        matchUserPhone = byId.get().getPhone();
                    }
                }
                // 当前退租信息
                Optional<HouseSurrender> optional = houseSurrenderRepository.findById(vo.getId());
                // 把当前合同信息存储
                if (optional.isPresent()) {
                    HouseSurrender entity = optional.get();
                    entity.setStartTime(vvo.getStartTime());
                    entity.setEndTime(vvo.getEndTime());
                    entity.setMatchUserName(matchUserName);
                    entity.setMatchUserPhone(matchUserPhone);
                    entity.setContractStatus(vvo.getContractStatus());
                    entity.setUnpaidAmount(vvo.getCostConvert());
                    houseSurrenderRepository.save(entity);
                }
            }

            // 修改房屋状态、损坏原因
            Integer integer = houseSurrenderRepository.updateHouseStatusAndDamageReasonByIdAndHouseId(
                    vo.getHouseStatus(), vo.getDamageReason(), vo.getId(), vo.getHouseId());
            if (integer > 0) {
                // 修改房屋状态成功后修改退租状态、合同结束时间
                Integer updateStatusByIdAndHouseId = houseSurrenderRepository.updateStatusByIdAndHouseId(2, vo.getId(),
                        vo.getHouseId());
                if (updateStatusByIdAndHouseId > 0) {
                    return ResultBean.successfulResult(null);
                }
            }

            return ResultBean.failedResultWithMsg("验房失败");
        } catch (Exception e) {
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 查验合同
     * 退租id,房屋id
     *
     * @param accessToken
     * @param id          退租id
     * @param houseId     房屋id
     *                    查询当前账单退租信息
     */
    public ResultBean<ApiData<HouseSurrenderVO>> checkTheContract(String accessToken, Long id, String houseId) {
        try {
            // 校验登录信息
            TokenRedisVo tokenVo = redisService.findTokenVo(accessToken);
            // System.out.println(id + "aa/t"+houseId);
            if (tokenVo == null) {
                return ResultBean.failedResultOfToken();
            }
            Long orgId = tokenVo.getEnterpriseId();

            // 退租管理信息
            HouseSurrender entity = houseSurrenderRepository.findByIdAndHouseId(id, houseId);
            if (entity == null) {
                return ResultBean.failedResultWithMsg("退租信息有误");
            }

            BigDecimal cost = new BigDecimal("0");
            BigDecimal totalCost = new BigDecimal("0");
            List<HouseSurrenderVO> houseSurrenderVOS = new ArrayList<>();
            // 退租状态：0无 1已申请 2已验房 3未结清 4已结清 6已完成
            if (entity.getStatus() == 0 || entity.getStatus() == 1 || entity.getStatus() == 2) {

                // 房屋id、成员名字、成员手机号、开始时间、结束时间、房屋状态
                String querySql = "SELECT m.house_id,shea.name AS matchUserName, m.damage_reason AS damageReason, shea.mobile AS matchUserPhone,shc.start_time AS startTime,shc.ent_time AS endTime,m.house_status AS houseStatus,m.id , m.surreender_type as surrenderType , m.remark as remark ,m.other_amount_type as otherAmountType \n" +
                        "FROM m_house_surrender m\n" +
                        "LEFT JOIN saas_house sh\n" +
                        "ON m.house_id = sh.id\n" +
                        "LEFT JOIN saas_house_match_user shmu\n" +
                        "ON shmu.house_id = sh.id\n" +
                        "LEFT JOIN saas_house_eligible_apply shea\n" +
                        "ON shea.id = shmu.apply_id\n" +
                        "LEFT JOIN saas_house_contract_ref shcr\n" +
                        "ON shcr.house_id = sh.id\n" +
                        "LEFT JOIN saas_house_contract shc\n" +
                        "ON shc.contract_nubmer = shcr.contract_number\n" +
                        "WHERE shmu.status = 1\n" +
                        "UNION ALL\n" +
                        "SELECT m.house_id,mus.name AS matchUserName, m.damage_reason AS damageReason, mus.mobile AS matchUserPhone,muh.start_date AS startTime,muh.end_date AS endTime,m.house_status AS houseStatus,m.id , m.surreender_type as surrenderType , m.remark as remark,m.other_amount_type as otherAmountType\n" +
                        "FROM m_house_surrender m\n" +
                        "LEFT JOIN saas_house sh\n" +
                        "ON m.house_id = sh.id\n" +
                        "LEFT JOIN m_match_user_house muh\n" +
                        "ON sh.id = muh.house_id\n" +
                        "LEFT JOIN (SELECT h1.community_house_id,h1.name,h1.mobile,h1.status FROM saas_community_house_user h1 WHERE h1.type = 1 AND h1.status = 1) mus \n" +
                        "ON mus.community_house_id = muh.house_id \n";

                List<Object> args = new ArrayList<>();
                StringBuffer where = new StringBuffer(" where 1 = 1 ");
                String sql1 = "SELECT * FROM (" + querySql + ") t";

                if (StringUtils.isNotBlank(houseId)) {
                    where.append(" and t.house_id = ? ");
                    args.add(houseId);
                }
                if (id != null) {
                    where.append(" and t.id = ? ");
                    args.add(id);
                }

                houseSurrenderVOS = jdbcTemplate.query(sql1 + where, new BeanPropertyRowMapper<>(HouseSurrenderVO.class), args.toArray());
                List<Object> listargs = new ArrayList<>();
                for (int i = 0; i < 4; i++) {
                    listargs.add(houseId);
                }

                String queryCost = "SELECT SUM(cost) AS cost FROM (\n" +
                        "SELECT eeb.surplus_cost + IFNULL(eab.adjust_cost,0) as cost  FROM saas_estate_expense_bill eeb left join saas_estate_adjust_bill eab on  eab.bill_type = 2\n" +
                        "and  eeb.id = eab.bill_id  and eab.adjust_type = 0 and  `status` = 0 WHERE (eeb.payment_status in  (0,2) OR eeb.payment_status IS NULL) AND eeb.audit_status = 1 AND eeb.house_id = ? And eeb.org_id = " + orgId + "\n" +
                        "UNION ALL\n" +
                        "SELECT eeb.cost FROM saas_rent_bill eeb WHERE (eeb.payment_status in  (0,2) OR eeb.payment_status IS NULL) AND eeb.audit_status = 1 AND eeb.house_id = ? And eeb.org_id = " + orgId + " \n" +
                        "UNION ALL\n" +
                        "SELECT eeb.surplus_cost + IFNULL(eab.adjust_cost,0) as cost FROM saas_estate_rent_bill eeb left join saas_estate_adjust_bill eab on  eab.bill_type = 1\n" +
                        "and  eeb.id = eab.bill_id  and eab.adjust_type = 0 and  `status` = 0 WHERE (eeb.payment_status in  (0,2) OR eeb.payment_status IS NULL) AND eeb.audit_status = 1 AND eeb.house_id = ? And eeb.org_id = " + orgId + "\n" +
                        "UNION ALL\n" +
                        "SELECT eeb.cost FROM saas_expense_bill eeb WHERE (eeb.payment_status in  (0,2) OR eeb.payment_status IS NULL) AND eeb.audit_status = 1 AND eeb.house_id = ? And eeb.org_id = " + orgId + "\n" +
                        ") t";
                String queryTotalCost = "SELECT SUM(cost) AS cost FROM (\n" +
                        "SELECT eeb.surplus_cost + IFNULL(eab.adjust_cost,0) as cost  FROM saas_estate_expense_bill eeb left join saas_estate_adjust_bill eab on  eab.bill_type = 2\n" +
                        "and  eeb.id = eab.bill_id  and eab.adjust_type = 0 and  `status` = 0 WHERE (eeb.payment_status in  (0,2) OR eeb.payment_status IS NULL) AND eeb.audit_status = 1 AND eeb.house_id = ? \n" +
                        "UNION ALL\n" +
                        "SELECT eeb.cost FROM saas_rent_bill eeb WHERE (eeb.payment_status in  (0,2) OR eeb.payment_status IS NULL) AND eeb.audit_status = 1 AND eeb.house_id = ? \n" +
                        "UNION ALL\n" +
                        "SELECT eeb.surplus_cost + IFNULL(eab.adjust_cost,0) as cost FROM saas_estate_rent_bill eeb left join saas_estate_adjust_bill eab on  eab.bill_type = 1 \n" +
                        "and  eeb.id = eab.bill_id  and eab.adjust_type = 0 and  `status` = 0 WHERE (eeb.payment_status in  (0,2) OR eeb.payment_status IS NULL) AND eeb.audit_status = 1 AND eeb.house_id = ? \n" +
                        "UNION ALL\n" +
                        "SELECT eeb.cost FROM saas_expense_bill eeb WHERE (eeb.payment_status in  (0,2) OR eeb.payment_status IS NULL) AND eeb.audit_status = 1 AND eeb.house_id = ? \n" +
                        ") t";


                String adjustCostSql = "SELECT SUM(cost) AS cost\n" +
                        "FROM (SELECT IFNULL(eab.adjust_cost, 0) AS cost\n" +
                        "      FROM saas_estate_expense_bill eeb\n" +
                        "               LEFT JOIN saas_estate_adjust_bill eab ON eab.bill_type = 2\n" +
                        "          AND eeb.id = eab.bill_id\n" +
                        "          AND eab.adjust_type = 0\n" +
                        "          AND `status` = 0\n" +
                        "      WHERE eeb.payment_status = 1\n" +
                        "        AND eeb.audit_status = 1\n" +
                        "        AND eeb.house_id = ? \n" +
                        "      UNION ALL\n" +
                        "      SELECT eeb.cost\n" +
                        "      FROM saas_rent_bill eeb\n" +
                        "      WHERE eeb.payment_status = 1\n" +
                        "        AND eeb.audit_status = 1\n" +
                        "        AND eeb.house_id = ? \n" +
                        "      UNION ALL\n" +
                        "      SELECT IFNULL(eab.adjust_cost, 0) AS cost\n" +
                        "      FROM saas_estate_rent_bill eeb\n" +
                        "               LEFT JOIN saas_estate_adjust_bill eab ON eab.bill_type = 1\n" +
                        "          AND eeb.id = eab.bill_id\n" +
                        "          AND eab.adjust_type = 0\n" +
                        "          AND `status` = 0\n" +
                        "      WHERE eeb.payment_status = 1\n" +
                        "        AND eeb.audit_status = 1\n" +
                        "        AND eeb.house_id = ? \n" +
                        "      UNION ALL\n" +
                        "      SELECT eeb.cost\n" +
                        "      FROM saas_expense_bill eeb\n" +
                        "      WHERE eeb.payment_status = 1\n" +
                        "        AND eeb.audit_status = 1\n" +
                        "        AND eeb.house_id = ? ) t";

                Integer adjustCost = jdbcTemplate.queryForObject(adjustCostSql, Integer.class, listargs.toArray());


                // 房屋所有费用
                List<HouseSurrenderVO> costList = jdbcTemplate.query(queryCost, new BeanPropertyRowMapper<>(HouseSurrenderVO.class), listargs.toArray());
                HouseSurrenderVO costs = costList.get(0);
                List<HouseSurrenderVO> totalList = jdbcTemplate.query(queryTotalCost, new BeanPropertyRowMapper<>(HouseSurrenderVO.class), listargs.toArray());
                HouseSurrenderVO totalCosts = totalList.get(0);
                if (costs != null) {
                    cost = costs.getCost() == null ? new BigDecimal("0") : fen2yuan(costs.getCost());
                    cost = cost.add(fen2yuan(adjustCost));
                }
                if (totalCosts != null) {
                    totalCost = totalCosts.getCost() == null ? new BigDecimal("0") : fen2yuan(totalCosts.getCost());
                    totalCost = totalCost.add(fen2yuan(adjustCost));
                }


                Date date = new Date();
                for (HouseSurrenderVO item : houseSurrenderVOS) {
                    if (item.getEndTime() != null) {
                        if (item.getEndTime().getTime() < date.getTime()) {
                            // 合同已过期
                            item.setContractStatus(2);
                        } else {
                            item.setContractStatus(1);
                        }
                    }
                    String houseId1 = item.getHouseId();
                    String matchUserName = null;
                    String matchUserPhone = null;
                    // 获取承租人信息
                    List<MatchUserHouse> byHouseIdList = matchUserHouseRepository.findByHouseId(houseId1);
                    if (!byHouseIdList.isEmpty()) {
                        Long matchUserStockId = Long.valueOf(byHouseIdList.get(0).getMatchUserStockId());
                        Optional<MatchUser> byId = matchUserRepository.findById(matchUserStockId);
                        if (byId.isPresent()) {
                            matchUserName = byId.get().getName();
                            matchUserPhone = byId.get().getPhone();
                        }
                    }
                    item.setMatchUserName(matchUserName);
                    item.setMatchUserPhone(matchUserPhone);
                    item.setCostConvert(cost);
                    // 未缴费账单金额
                    item.setUnpaidAmount(cost);
                    // 全机构未缴费金额
                    item.setTotalUnpaidAmount(totalCost);
                    // 租金
                    item.setRentAmount(entity.getRentAmount() == null ? new BigDecimal("0") : entity.getRentAmount());
                    // 押金
                    item.setCashPledgeAmount(entity.getCashPledgeAmount() == null ? new BigDecimal("0") : entity.getCashPledgeAmount());
                    // 其他收费
                    item.setOtherAmount(entity.getOtherAmount() == null ? new BigDecimal("0") : entity.getOtherAmount());
                    // 结算金额
                    item.setSettlementAmount(entity.getSettlementAmount() == null ? new BigDecimal("0") : entity.getSettlementAmount());
                    // 退租原因
                    item.setSurreenderType(entity.getSurreenderType());

                    HouseRemainingRelation relation = houseRemainingRelationRepository.findByOrgIdAndHouseId(orgId,houseId);
                    if (relation != null) {
                        Optional<HouseRemaining> byId = houseRemainingRepository.findById(relation.getRemainingId());
                        HouseRemaining houseRemaining = byId.get();
                        Integer balanceFee = houseRemaining.getBalanceFee();
                        Integer cashPledge = houseRemaining.getCashPledge();
                        item.setBalanceFee(fen2yuan(balanceFee).doubleValue());
                        item.setCashPledge(fen2yuan(cashPledge).doubleValue());
                    }

                }
            } else {
                // 退租管理
                cost = entity.getUnpaidAmount();
                HouseSurrenderVO vo = new HouseSurrenderVO();
                vo.setCostConvert(cost);
                vo.setDamageReason(entity.getDamageReason());
                vo.setUnpaidAmount(cost);
                vo.setTotalUnpaidAmount(totalCost);
                vo.setHouseId(entity.getHouseId());
                vo.setMatchUserName(entity.getMatchUserName());
                vo.setMatchUserPhone(entity.getMatchUserPhone());
                vo.setStartTime(entity.getStartTime());
                vo.setEndTime(entity.getEndTime());
                vo.setHouseStatus(entity.getHouseStatus());
                vo.setContractStatus(entity.getContractStatus());
                vo.setId(entity.getId());
                vo.setUnpaidAmount(entity.getUnpaidAmount());
                vo.setRentAmount(entity.getRentAmount() == null ? new BigDecimal("0") : entity.getRentAmount());
                vo.setCashPledgeAmount(entity.getCashPledgeAmount() == null ? new BigDecimal("0") : entity.getCashPledgeAmount());
                vo.setOtherAmount(entity.getOtherAmount() == null ? new BigDecimal("0") : entity.getOtherAmount());
                vo.setSettlementAmount(entity.getSettlementAmount() == null ? new BigDecimal("0") : entity.getSettlementAmount());
                vo.setSurreenderType(entity.getSurreenderType());
                vo.setRemark(entity.getRemark());
                vo.setOtherAmountType(entity.getOtherAmountType());

                HouseRemainingRelation relation = houseRemainingRelationRepository.findByOrgIdAndHouseId(orgId,houseId);
                if (relation != null) {
                    Optional<HouseRemaining> byId = houseRemainingRepository.findById(relation.getRemainingId());
                    HouseRemaining houseRemaining = byId.get();
                    Integer balanceFee = houseRemaining.getBalanceFee();
                    Integer cashPledge = houseRemaining.getCashPledge();
                    vo.setBalanceFee(fen2yuan(balanceFee).doubleValue());
                    vo.setCashPledge(fen2yuan(cashPledge).doubleValue());
                }

                houseSurrenderVOS.add(vo);
            }

            ApiData<HouseSurrenderVO> apiData = new ApiData<>();
            apiData.setData(houseSurrenderVOS);
            return ResultBean.successfulResult(apiData);
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 生成退租账单 修改退租表金额，物业和公租房都要新增，saas_expense_bill saas_expense_bill_relation
     * 承租人id
     *
     * @param accessToken
     * @param vo
     * @return
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultBean<Object> saveLeasebackBill(String accessToken, SaveExpenseBillVOAndSurrenderVO vo) {
        TokenRedisVo tokenVo = redisService.findTokenVo(accessToken);
        if (tokenVo == null) {
            return ResultBean.failedResultOfToken();
        }

        Long orgId = tokenVo.getEnterpriseId();
        String userId = tokenVo.getId();
        int otherAmount = 0;
        if (vo.getOtherAmount() != null) {
            otherAmount = DataUtils.doubleToInteger(vo.getOtherAmount().doubleValue());
        }
        Date date = new Date();
        if (orgId == null) {
            return ResultBean.failedResultWithMsg("未找到当前登录用户企业");
        }
        if (vo.getSettlementAmount() == null) {
            return ResultBean.failedResultWithMsg("结算金额不能为空");
        }

        if (vo.getSurrenderId() == null) {
            return ResultBean.failedResultWithMsg("退租ID不能为空");
        }

        Integer otherAmountType = vo.getOtherAmountType();
        if (otherAmountType == null) {
            return ResultBean.failedResultWithMsg("其他费用类型不能为空");
        }

        String sql = "SELECT eeb.surplus_cost AS cost,eeb.id as id , eeb.house_id as houseId ,eeb.charge_type as chargeType ,eeb.start_time as startTime,eeb.end_time as endTime ,eeb.org_id as orgId FROM saas_estate_expense_bill eeb WHERE ( eeb.payment_status in (0,2) OR eeb.payment_status IS NULL )  AND eeb.audit_status = 1  AND eeb.house_id = ? \n" +
                "UNION ALL\n" +
                "SELECT eeb.cost ,eeb.id as id , eeb.house_id as houseId ,eeb.charge_type as chargeType ,eeb.start_time as startTime,eeb.end_time as endTime ,eeb.org_id as orgId FROM saas_rent_bill eeb  WHERE ( eeb.payment_status in (0,2) OR eeb.payment_status IS NULL )  AND eeb.audit_status = 1  AND eeb.house_id = ? \n" +
                "UNION ALL\n" +
                "SELECT eeb.surplus_cost AS cost ,eeb.id as id , eeb.house_id as houseId ,IF(eeb.charge_type = 1,8,9)as chargeType ,eeb.start_time as startTime,eeb.end_time as endTime ,eeb.org_id as orgId FROM saas_estate_rent_bill eeb  WHERE ( eeb.payment_status in (0,2) OR eeb.payment_status IS NULL )  AND eeb.audit_status = 1  AND eeb.house_id = ? \n" +
                "UNION ALL\n" +
                "SELECT eeb.cost ,eeb.id as id , eeb.house_id as houseId ,eeb.charge_type as chargeType ,eeb.start_time as startTime,eeb.end_time as endTime ,eeb.org_id as orgId FROM saas_expense_bill eeb  WHERE  ( eeb.payment_status in (0,2) OR eeb.payment_status IS NULL )  AND eeb.audit_status = 1  AND eeb.house_id = ?";

        String totalSql = "SELECT IFNULL(SUM(t.cost),0) as cost FROM ( " + sql + " ) t WHERE t.orgId = ? ";


        String violateSql = "SELECT ifNUll(SUM(c.cost),0) FROM ( \n " +
                "SELECT vb.cost FROM saas_estate_violate_bill vb \n " +
                "left join  saas_estate_expense_bill eb on vb.bill_id = eb.id \n " +
                "WHERE  vb.bill_type in (1,2,3,4,5,6,7) and vb.house_id = ? and eb.org_id = ? \n " +
                "UNION ALL \n " +
                "SELECT vb.cost FROM saas_estate_violate_bill vb \n " +
                "left join  saas_estate_rent_bill rb on vb.bill_id = rb.id  \n " +
                "WHERE vb.bill_type in (8,9) and vb.house_id = ? and rb.org_id = ? ) c";

        Optional<HouseSurrender> byId = houseSurrenderRepository.findById(vo.getSurrenderId());
        if (byId.isPresent()) {
            HouseSurrender entity = byId.get();
            if (entity.getStatus() == 2) {
                Optional<House> houseOpt = houseRepository.findById(entity.getHouseId());
                if (!houseOpt.isPresent()) {
                    return ResultBean.failedResultWithMsg("房屋信息未找到");
                }
                House house = houseOpt.get();


                List<String> houseIds = new ArrayList<>();
                for (int i = 0; i < 4; i++) {
                    houseIds.add(house.getId());
                }

                List<BillListVo> vos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BillListVo.class), houseIds.toArray());

                String matchUserId = "";
                String matchUserName = "";

                List<EstateViolateBill> violateBills = estateViolateBillRepository.findByHouseId(house.getId());
                if (CollectionUtil.isNotEmpty(violateBills)) {
                    for (EstateViolateBill violateBill : violateBills) {
                        violateBill.setStatus(1);
                    }
                    estateViolateBillRepository.saveAll(violateBills);
                }

                List<CommunityHouseUser> uList = communityHouseUserRepository
                        .findByCommunityHouseIdAndStatusAndType(house.getId(), 1, 1);
                if (!uList.isEmpty()) {
                    CommunityHouseUser communityHouseUser = uList.get(0);
                    matchUserId = communityHouseUser.getUserId();
                    matchUserName = communityHouseUser.getName();
                }
                if (StringUtils.isEmpty(matchUserId) || StringUtils.isEmpty(matchUserName)) {
                    List<MatchUserHouse> matchUserHouses = matchUserHouseRepository.findByHouseId(house.getId());
                    String matchUserStockId = matchUserHouses.get(0).getMatchUserStockId();
                    //	根据承租人和房产的承租人id查询承认人表的房产承租人id
                    Optional<MatchUser> matchUserRepositoryById = matchUserRepository.findById(Long.parseLong(matchUserStockId));
                    if (matchUserRepositoryById.isPresent()) {
                        // 产权下的用户承租人id
                        MatchUser byMatchUserId = matchUserRepositoryById.get();
                        // 产权下的用户承租人id
                        if (StringUtils.isEmpty(matchUserId)) {
                            matchUserId = byMatchUserId.getMatchUserId();
                            matchUserName = matchUserHouses.get(0).getCompanyName();
                        }
                    }
                }

                if (StrUtil.isEmpty(matchUserName) && StrUtil.isNotEmpty(matchUserId)) {
                    matchUserName = appUserRepository.findById(matchUserId).get().getName();
                }

                // 房屋对应的产权单位以及物业 id
                List<Long> orgIds = new ArrayList<>();
                Long orgId1 = house.getOrgId();
                if (orgId1 != null) {
                    orgIds.add(orgId1);
                }
                Long housePropertyId = house.getHousePropertyId();
                if (housePropertyId != null && !housePropertyId.equals(orgId1)) {
                    orgIds.add(housePropertyId);
                }

                entity.setOtherAmountType(otherAmountType);
                for (Long id : orgIds) {
                    houseIds.add(id.toString());
                    Integer totalCost = jdbcTemplate.queryForObject(totalSql, Integer.class, houseIds.toArray());
                    houseIds.remove(4);
                    if (orgId.equals(id)) {
                        //  otherAmountType 0退费 1补缴
                        if (otherAmountType == 1) {
                            totalCost += otherAmount;
                        } else if (otherAmountType == 0) {
                            totalCost -= otherAmount;
                        }
                    }

                    List<Object> args = new ArrayList<>();
                    for (int i = 0; i < 2; i++) {
                        args.add(house.getId());
                        args.add(id);
                    }

                    Integer violateCost = jdbcTemplate.queryForObject(violateSql, Integer.class, args.toArray());
                    HouseRemainingRelation repository = houseRemainingRelationRepository.findByOrgIdAndHouseId(id, house.getId());
                    if (repository == null) {
                        return ResultBean.failedResultWithMsg("房屋账户未找到");
                    }

                    Optional<HouseRemaining> remainingOptional = houseRemainingRepository.findById(repository.getRemainingId());
                    if (!remainingOptional.isPresent()) {
                        return ResultBean.failedResultWithMsg("房屋账户未找到");
                    }

                    HouseRemaining rem = remainingOptional.get();
                    Integer balanceFee = rem.getBalanceFee();
                    Integer cashPledge = rem.getCashPledge();
                    Integer remCost = balanceFee + cashPledge;

                    totalCost = remCost - totalCost;


                    // 退费记录
                    HouseReturnPremium premium = new HouseReturnPremium();
                    premium.setHouseId(house.getId());
                    premium.setOrgId(id);
                    premium.setCreateTime(date);
                    premium.setCreateId(userId);
                    // 缴费状态：0未缴费 1已缴费
                    if (0 == totalCost) {
                        premium.setPaymentStatus(1);
                        premium.setPaymentTime(date);
                        premium.setOperatorId(redisService.getSystemUser());
                    } else {
                        premium.setPaymentStatus(0);
                    }
                    premium.setMatchUserId(matchUserId);
                    premium.setMatchUserName(matchUserName);
                    // 类型：0退费 1补缴
                    if (0 > totalCost) {
                        premium.setType(1);
                    } else {
                        premium.setType(0);
                    }
                    premium.setCost(Math.abs(totalCost));
                    premium.setOtherCost(otherAmount);
                    premium.setRemainingId(rem.getId());
                    // 关联上退租id
                    premium.setSurrenderId(vo.getSurrenderId());
                    premium.setViolateCost(violateCost);
                    HouseReturnPremium returnSave = houseReturnPremiumRepository.save(premium);

                    // 退费记录与账单关系
                    for (BillListVo vo1 : vos) {
                        if (vo1.getOrgId().equals(id)) {
                            // 租金押金
                            if (vo1.getChargeType() == 9 || vo1.getChargeType() == 8) {
                                Optional<EstateRentBill> byId1 = estateRentBillRepository.findById(vo1.getId());
                                if (byId1.isPresent()) {
                                    EstateRentBill rentBill = byId1.get();
                                    // rentBill.setSurplusCost(0);
                                    rentBill.setPaymentStatus(1);
                                    rentBill.setPaymentTime(date);
                                    rentBill.setAdjustStatus(1);
                                    estateRentBillRepository.save(rentBill);
                                }
                            } else {
                                Optional<EstateExpenseBill> byId1 = estateExpenseBillRepository.findById(vo1.getId());
                                if (byId1.isPresent()) {
                                    EstateExpenseBill expenseBill = byId1.get();
                                    // expenseBill.setSurplusCost(0);
                                    expenseBill.setPaymentStatus(1);
                                    expenseBill.setPaymentTime(date);
                                    expenseBill.setAdjustStatus(1);
                                    estateExpenseBillRepository.save(expenseBill);
                                }
                            }

                            HouseReturnBillRelation relation = new HouseReturnBillRelation();
                            relation.setBillId(vo1.getId());
                            relation.setReturnId(returnSave.getId());
                            relation.setBillType(vo1.getChargeType());
                            houseReturnBillRelationRepository.save(relation);
                        }
                    }

                }



                // 这个状态根据传过来的退租id查询出来
                Calendar rightNow = Calendar.getInstance();
                rightNow.setTime(date);
                rightNow.set(Calendar.HOUR_OF_DAY, 0);
                rightNow.set(Calendar.MINUTE, 0);
                rightNow.set(Calendar.SECOND, 0);
                rightNow.set(Calendar.MILLISECOND, 0);

                Calendar rightNow1 = Calendar.getInstance();
                rightNow1.setTime(date);
                rightNow1.add(Calendar.MONTH, 1);

                Long billId = null;
                String phone = null;

                // 41 物业 退租
                // 42 公租房 退租
                Integer type = null;
                // 性质:1公租房、2廉租房、3商品房、4经济适用房、5其它
                if (house.getHouseNature() == 1) {
                    // 生成公租房管理中的其他账单
                    ExpenseBill expenseBill = new ExpenseBill();
                    expenseBill.setBillType(1);
                    expenseBill.setHouseId(house.getId());
                    expenseBill.setCommunityId(house.getResidenceCommunityId() + "");
                    expenseBill.setBuildingNum(house.getBuildingNum());
                    expenseBill.setUnit(house.getUnit());
                    expenseBill.setRoomNumber(house.getRoomNumber());
                    // 操作生成账单这个用户所在的机构ID
                    expenseBill.setOrgId(tokenVo.getEnterpriseId());

                    // 查询公租房承租人
                    List<HouseMatchUser> uList1 = houseMatchUserRepository.findHouseIdAndStatus(house.getId(), 1);
                    if (uList1.isEmpty()) {
                        return ResultBean.failedResultWithMsg("公租房无承租人信息, 无法生成账单");
                    }
                    expenseBill.setMatchUserId(uList1.get(0).getMatchUserId());
                    expenseBill.setChargeType(10);
                    expenseBill.setCost(yuan2fen(BigDecimal.valueOf(Math.abs(vo.getSettlementAmount().doubleValue()))));
                    expenseBill.setStartTime(rightNow.getTime());
                    expenseBill.setEndTime(rightNow1.getTime());
                    expenseBill.setCreateTime(date);
                    expenseBill.setAuditStatus(1);
                    expenseBill.setPaymentStatus(0);
                    expenseBill.setOperatorId(tokenVo.getId());
                    expenseBill.setLastUpdateTime(date);
                    expenseBill.setShouldPayTime(rightNow1.getTime());
                    expenseBillRepository.save(expenseBill);

                    ExpenseBillRelation expenseBillRelation = new ExpenseBillRelation();
                    expenseBillRelation.setHouseId(house.getId());
                    expenseBillRelation.setExpenseBillId(expenseBill.getId());
                    expenseBillRelation.setMatchUserId(uList1.get(0).getMatchUserId());
                    expenseBillRelationRepository.save(expenseBillRelation);

                    // 产生其它费用待缴费明细
                    HouseWaitPaymentDetail houseWaitPaymentDetail = new HouseWaitPaymentDetail();
                    houseWaitPaymentDetail.setExpenseBillId(expenseBill.getId());
                    houseWaitPaymentDetail.setChargeType(expenseBill.getChargeType());
                    houseWaitPaymentDetailRepository.save(houseWaitPaymentDetail);

                    billId = expenseBill.getId();
                    matchUserId = expenseBill.getMatchUserId();
                    Optional<AppUser> optional = appUserRepository.findById(matchUserId);
                    if (optional.isPresent()) {
                        AppUser appUser = optional.get();
                        phone = appUser.getPhone();
                    }
                    type = 42;

                } else {
                    // 缓存合同信息
                    ResultBean<ApiData<HouseSurrenderVO>> resultBean = checkTheContract(accessToken, entity.getId(), entity.getHouseId());
                    if (resultBean.getCode().equals(StatusCode.SUCCESS_CODE_10000.getErrorCode())
                            && resultBean.getResultData() != null) {
                        List<HouseSurrenderVO> list = resultBean.getResultData().getData();
                        HouseSurrenderVO vvo = list.get(0);
                        entity.setStartTime(vvo.getStartTime());
                        entity.setEndTime(vvo.getEndTime());
                        entity.setMatchUserName(vvo.getMatchUserName());
                        entity.setMatchUserPhone(vvo.getMatchUserPhone());
                        entity.setContractStatus(vvo.getContractStatus());
                        entity.setUnpaidAmount(vvo.getCostConvert());
                    }

                    // 修改退租表的金额
                /*houseSurrenderRepository.updatemoney(new BigDecimal(Math.abs(vo.getSettlementAmount().doubleValue())),
                        vo.getRentAmount(), vo.getCashPledgeAmount(), vo.getOtherAmount(), vo.getSettlementAmount(),
                        billId, vo.getSurrenderId());*/

                    entity.setRentAmount(vo.getRentAmount());
                    entity.setCashPledgeAmount(vo.getCashPledgeAmount());
                    entity.setOtherAmount(vo.getOtherAmount());
                    entity.setSettlementAmount(vo.getSettlementAmount());
                    entity.setBillId(billId);
                    entity.setStatus(3);
                    houseSurrenderRepository.save(entity);

                        /*// 物业中的生成其他账单
                        EstateExpenseBill estateExpenseBill = new EstateExpenseBill();
                        estateExpenseBill.setBillType(1);
                        estateExpenseBill.setHouseId(house.getId());
                        estateExpenseBill.setCommunityId(house.getResidenceCommunityId() + "");
                        estateExpenseBill.setBuildingNum(house.getBuildingNum());
                        estateExpenseBill.setUnit(house.getUnit());
                        estateExpenseBill.setRoomNumber(house.getRoomNumber());
                        estateExpenseBill.setMeasureUnit("元");
                        // 操作生成账单这个用户所在的机构ID
                        estateExpenseBill.setOrgId(tokenVo.getEnterpriseId());

                        // 查询非公租房承租人
                        List<CommunityHouseUser> uList = communityHouseUserRepository
                                .findByCommunityHouseIdAndStatusAndType(house.getId(), 1, 1);
                        if (!uList.isEmpty()) {
                            // return ResultBean.failedResultWithMsg("非公租房无承租人信息,
                            // 无法生成账单");
                            estateExpenseBill.setMatchUserId(uList.get(0).getUserId());
                            phone = uList.get(0).getMobile();
                        }

                        estateExpenseBill.setChargeType(10);
                        int cost = yuan2fen(BigDecimal.valueOf(Math.abs(vo.getSettlementAmount().doubleValue())));
                        estateExpenseBill.setCost(cost);
                        estateExpenseBill.setSurplusCost(cost);
                        estateExpenseBill.setStartTime(date);
                        estateExpenseBill.setEndTime(rightNow1.getTime());
                        estateExpenseBill.setCreateTime(date);
                        estateExpenseBill.setAuditStatus(1);
                        estateExpenseBill.setPaymentStatus(0);
                        estateExpenseBill.setOperatorId(tokenVo.getId());
                        estateExpenseBill.setLastUpdateTime(date);
                        estateExpenseBill.setShouldPayTime(rightNow1.getTime());
                        estateExpenseBillRepository.save(estateExpenseBill);

                        // 产生其它费用待缴费明细
                        EstateExpenseBillDetail estateExpenseBillDetail = new EstateExpenseBillDetail();
                        estateExpenseBillDetail.setExpenseBillId(estateExpenseBill.getId());
                        // 1水费 2电费 3物业费 4宽带费 5电视费 6煤气费 7暖气费 10退租
                        estateExpenseBillDetail.setChargeType(estateExpenseBill.getChargeType());
                        estateExpenseBillDetailRepository.save(estateExpenseBillDetail);

                        billId = estateExpenseBill.getId();
                        matchUserId = estateExpenseBill.getMatchUserId();
                        type = 41;
                    }

                    // 查询房屋
                    /*String queryHouseInfo = "SELECT sc.name AS residenceCommunityName,b.name AS buildingNumName,u.name AS unitName,sh.room_number AS roomNumber FROM saas_house sh\n"
                            + "LEFT JOIN saas_community sc\n" + "ON sh.residence_community_id = sc.id\n"
                            + "LEFT JOIN saas_community_build b\n" + "ON sh.building_num = b.id\n"
                            + "LEFT JOIN saas_community_build_unit u\n" + "ON sh.unit = u.id " + "WHERE sh.id = ? ";
                    HouseVO houseVO = jdbcTemplate.queryForObject(queryHouseInfo,
                            new BeanPropertyRowMapper<>(HouseVO.class), house.getId());

                    // 产生待办事项
                    BacklogItem backlogItem = new BacklogItem();
                    backlogItem.setHouseId(house.getId()); // 房id
                    backlogItem.setUserOrgId(tokenVo.getEnterpriseId()); // 个人组织id

                    backlogItem.setType(type); // 类型：1公租房录入 2智能锁(租金/押金) 3其它费用账单
                    // 4资格申请-配租 5备案 6合同到期 13智能锁个人绑定确认
                    // 14门锁低电量告警 15门锁离线:
                    backlogItem.setTitle("退租账单"); // 标题
                    backlogItem.setContent("您的" + houseVO.getResidenceCommunityName() + houseVO.getBuildingNumName() + "栋"
                            + houseVO.getUnitName() + "单元" + houseVO.getRoomNumber() + "号房屋已产生退租账单待缴费，金额为"
                            + Math.abs(vo.getSettlementAmount().doubleValue()) + "元"); // 内容
                    backlogItem.setBusinessStatus("待缴费");
                    backlogItem.setHandleStatus(0); // 事项处理状态：0未读/未处理 1已读/已处理
                    backlogItem.setOperatorId(tokenVo.getId()); // 操作人id
                    backlogItem.setMatchUserId(matchUserId); // 承租人id
                    backlogItem.setPhone(phone); // 手机号
                    backlogItem.setCreateTime(new Date()); // 创建时间
                    BacklogItem backlogItem1 = backlogItemRepository.save(backlogItem);

                    if (type == 42) {
                        BacklogItemHouseOtherBill houseOtherBill = new BacklogItemHouseOtherBill();
                        houseOtherBill.setBacklogItemId(backlogItem1.getId());
                        houseOtherBill.setBillId(billId);//租金账单id
                        backlogItemHouseOtherBillRepository.save(houseOtherBill);
                    } else {
                        BacklogItemPropertyOtherBill propertyOtherBill = new BacklogItemPropertyOtherBill();
                        propertyOtherBill.setBacklogItemId(backlogItem1.getId());
                        propertyOtherBill.setBillId(billId);
                        backlogItemPropertyOtherBillRepository.save(propertyOtherBill);
                    }*/


                    /*String[] strings = new String[]{String.valueOf(billId),
                            String.valueOf(Math.abs(vo.getSettlementAmount().doubleValue()))};
                    if (house.getHouseNature() == 1) {
                        // 公租房同住人 循环 根据承租人id和房屋id
                        String sql1 = "SELECT sheac.id,sheac.name,sheac.mobile FROM saas_house_eligible_apply_cohabit sheac\n"
                                + "LEFT JOIN saas_house_match_user shmu\n" + "ON shmu.apply_id = sheac.apply_id\n"
                                + "WHERE shmu.house_id = ? AND shmu.status =1";
                        List<CohabitVO> query = jdbcTemplate.query(sql1, new BeanPropertyRowMapper<>(CohabitVO.class),
                                house.getId());
                        if (query != null && !query.isEmpty()) {
                            BacklogItem tbacklogItem1 = new BacklogItem();
                            tbacklogItem1.setHouseId(house.getId()); // 房id
                            tbacklogItem1.setUserOrgId(tokenVo.getEnterpriseId()); // 个人组织id
                            tbacklogItem1.setType(42); // 类型：1公租房录入 2智能锁(租金/押金)
                            // 3其它费用账单 4资格申请-配租 5备案
                            // 6合同到期 13智能锁个人绑定确认
                            // 14门锁低电量告警 15门锁离线:
                            //41 物业 退租
                            //42 公租房 退租

                            tbacklogItem1.setTitle("退租账单"); // 标题
                            tbacklogItem1
                                    .setContent("您的" + houseVO.getResidenceCommunityName() + houseVO.getBuildingNumName()
                                            + "栋" + houseVO.getUnitName() + "单元" + houseVO.getRoomNumber()
                                            + "号房屋已产生退租账单待缴费，金额为" + Math.abs(vo.getSettlementAmount().doubleValue()) + "元"); // 内容
                            tbacklogItem1.setBusinessStatus("待缴费");
                            tbacklogItem1.setHandleStatus(0); // 事项处理状态：0未读/未处理
                            // 1已读/已处理
                            tbacklogItem1.setOperatorId(tokenVo.getId()); // 操作人id
                            tbacklogItem1.setCreateTime(new Date()); // 创建时间
                            for (CohabitVO o : query) {
                                String queryId = "SELECT id FROM (\n" + "SELECT id,mobile FROM saas_user \n"
                                        + "UNION all \n" + "SELECT id,phone as mobile FROM saas_app_user \n" + ") t\n"
                                        + "WHERE t.mobile = ?\n";
                                List<String> query1 = jdbcTemplate.query(queryId, new BeanPropertyRowMapper<>(String.class),
                                        o.getMobile());
                                if (query1 != null && !query1.isEmpty()) {
                                    tbacklogItem1.setMatchUserId(query1.get(0)); // 同住人id
                                    tbacklogItem1.setMatchUserId(o.getMobile()); // 手机号

                                }
                                BacklogItem tbacklogItem2 = backlogItemRepository.save(tbacklogItem1);

                                BacklogItemHouseOtherBill houseOtherBills = new BacklogItemHouseOtherBill();
                                houseOtherBills.setBacklogItemId(tbacklogItem2.getId());
                                houseOtherBills.setBillId(billId);//租金账单id
                                backlogItemHouseOtherBillRepository.save(houseOtherBills);

                                // 给同住人发送短信
                                smsService.sendTemplateSmsWithData(o.getMobile(), smsConfig.getBillTemplateCode(), strings);
                            }
                        }
                    } else {
                        // 非公租房同住人
                        List<CommunityHouseUser> communityHouseUsers = communityHouseUserRepository
                                .findByCommunityHouseIdAndStatusAndType(house.getId(), 1, 2);
                        if (communityHouseUsers != null && !communityHouseUsers.isEmpty()) {
                            for (CommunityHouseUser communityHouseUser : communityHouseUsers) {
                                BacklogItem tbacklogItem1 = new BacklogItem();
                                backlogItem.setHouseId(house.getId()); // 房id
                                backlogItem.setUserOrgId(tokenVo.getEnterpriseId()); // 个人组织id
                                backlogItem.setType(41); // 类型：1公租房录入 2智能锁(租金/押金)
                                // 3其它费用账单 4资格申请-配租 5备案
                                // 6合同到期 13智能锁个人绑定确认
                                // 14门锁低电量告警 15门锁离线:
                                //41 物业 退租
                                //42 公租房 退租
                                backlogItem.setTitle("退租账单"); // 标题
                                tbacklogItem1.setContent("您的" + houseVO.getResidenceCommunityName()
                                        + houseVO.getBuildingNumName() + "栋" + houseVO.getUnitName() + "单元"
                                        + houseVO.getRoomNumber() + "号房屋已产生退租账单待缴费，金额为"
                                        + Math.abs(vo.getSettlementAmount().doubleValue()) + "元"); // 内容
                                backlogItem.setBusinessStatus("待缴费");
                                backlogItem.setHandleStatus(0); // 事项处理状态：0未读/未处理
                                // 1已读/已处理
                                backlogItem.setOperatorId(tokenVo.getId()); // 操作人id

                                String queryId = "SELECT id FROM (\n" + "SELECT id,mobile FROM saas_user \n"
                                        + "UNION all \n" + "SELECT id,phone as mobile FROM saas_app_user \n" + ") t\n"
                                        + "WHERE t.mobile = ?\n";
                                List<String> query1 = jdbcTemplate.query(queryId, new BeanPropertyRowMapper<>(String.class),
                                        communityHouseUser.getMobile());


                                if (query1 != null && !query1.isEmpty()) {
                                    tbacklogItem1.setMatchUserId(query1.get(0)); // 同住人id
                                    tbacklogItem1.setPhone(communityHouseUser.getMobile());// 手机号

                                }
                                backlogItem.setCreateTime(new Date()); // 创建时间
                                BacklogItem tbacklogItem2 = backlogItemRepository.save(tbacklogItem1);

                                BacklogItemPropertyOtherBill propertyOtherBill = new BacklogItemPropertyOtherBill();
                                propertyOtherBill.setBacklogItemId(tbacklogItem2.getId());
                                propertyOtherBill.setBillId(billId);

                                backlogItemPropertyOtherBillRepository.save(propertyOtherBill);

                                // 给同住人发送短信
                                smsService.sendTemplateSmsWithData(communityHouseUser.getMobile(),
                                        smsConfig.getBillTemplateCode(), strings);
                            }
                        }*/
                }

                return ResultBean.successfulResultWithMsg("账单成功生成", null);
            } else {
                return ResultBean.failedResultWithMsg("当前房屋未验房");
            }
        }


        return ResultBean.failedResultWithMsg("退租记录，数据有误");
    }

    /**
     * 账单支付后，修改退租状态 在支付那边写
     *
     * @param billd 退租账单id
     * @return
     */
    @Transactional
    public Integer changeStatus(Long billd, String houseId) {
        Integer updateStatusByIdAndHouseId = 0;
        try {
            updateStatusByIdAndHouseId = houseSurrenderRepository.updatePayFinshStatus(4, billd,
                    houseId);
            return updateStatusByIdAndHouseId;
        } catch (Exception e) {
            log.info(e.getMessage(), e);
        }
        return updateStatusByIdAndHouseId;
    }

    /**
     * 点击确认只要改状态 修改状态为已结清，然后把页面上的数据加入
     */
    @Transactional(rollbackFor = Exception.class)
    public ResultBean determine(String accessToken, HouseSurrenderVO houseSurrenderVO) {
        TokenRedisVo tokenVo = redisService.findTokenVo(accessToken);
        if (tokenVo == null) {
            return ResultBean.failedResultOfToken();
        }
        Optional<House> house = houseRepository.findById(houseSurrenderVO.getHouseId());
        if (house.isPresent()) {
            Optional<HouseSurrender> surrenderOpt = houseSurrenderRepository.findById(houseSurrenderVO.getId());
            if (!surrenderOpt.isPresent()) {
                return ResultBean.failedResultWithMsg("退租信息有误");
            }
            HouseSurrender entity = surrenderOpt.get();

            // 缓存合同信息
            ResultBean<ApiData<HouseSurrenderVO>> resultBean = checkTheContract(accessToken, houseSurrenderVO.getId(), houseSurrenderVO.getHouseId());
            if (resultBean.getCode().equals(StatusCode.SUCCESS_CODE_10000.getErrorCode())
                    && resultBean.getResultData() != null) {
                List<HouseSurrenderVO> list = resultBean.getResultData().getData();
                HouseSurrenderVO vo = list.get(0);
                entity.setStartTime(vo.getStartTime());
                entity.setEndTime(vo.getEndTime());
                entity.setMatchUserName(vo.getMatchUserName());
                entity.setMatchUserPhone(vo.getMatchUserPhone());
                entity.setContractStatus(vo.getContractStatus());
                entity.setUnpaidAmount(vo.getCostConvert());
            }

            entity.setRentAmount(houseSurrenderVO.getRentAmount());
            entity.setCashPledgeAmount(houseSurrenderVO.getCashPledgeAmount());
            entity.setOtherAmount(houseSurrenderVO.getOtherAmount());
            entity.setSettlementAmount(houseSurrenderVO.getSettlementAmount());
            entity.setStatus(4);
            houseSurrenderRepository.save(entity);
            return ResultBean.successfulResult("完成");
        }
        return ResultBean.failedResultWithMsg("房屋信息有误");
    }

    /**
     * 点击完成，改状态，删授权
     *
     * @param accessToken
     * @param id          退租id
     * @param houseId     房屋id
     *                    公租房 将公租房状态重置  更新承租人与资格申请记录状态为：失效  修改合同状态为过期 清理房屋授权关系
     *                    非公租房 删除房产所有成员信息 承租人无其它房产信息时，才删除承租人库数   清理房屋授权关系 删除房屋与承租人关系数据
     *                    <p>
     *                    重置临时密码授权次数
     *                    重置智能锁授权人数
     */
    @Transactional(rollbackFor = {RuntimeException.class, Exception.class})
    public ResultBean<Object> finishDraw(String accessToken, Long id, String houseId) {
        Date date = new Date();
        try {
            TokenRedisVo tokenVo = redisService.findTokenVo(accessToken);
            if (tokenVo == null) {
                return ResultBean.failedResultOfToken();
            }
            // List<House> byHousemateIdAndStatus = houseRepository.findByHousemateIdAndStatus(houseId, 1);
            // if (!byHousemateIdAndStatus.isEmpty()) {
            //    String id1 = byHousemateIdAndStatus.get(0).getId();
            //    houseId = id1;
            //}
            // 房屋智能锁id
            Long lockId = smartLockRepository.findByHouseIds(houseId);
            Optional<House> house = houseRepository.findById(houseId);
            if (house.isPresent()) {
                Integer houseNature = house.get().getHouseNature();
                if (houseNature == 1) {
                    // 公租房
                    // 公租房状态重置
                    houseRepository.updateWithDraw(houseId);
                    // 更新承租人与资格申请记录状态为：失效
                    houseMatchUserRepository.updateStatusByHouseId(houseId);
                    // 修改合同状态为过期
                    contractRepository.updateStatusByHouseId(houseId);
                    // 删除房屋与锁的授权关系，但是不删除房屋与锁的绑定
                    if (lockId != null) {
                        lockManagerService.deleteLockAuth(accessToken, lockId);
                    } else {
                        log.info("完成退租, 无智能锁绑定信息, houseId={}", houseId);
                    }
                } else {
                    // 非公租房
                    /*
                     * 需要删除房屋与承租人关系m_match_user_house，如果该承租人没有与其它房屋关联记录，承租人也需要删除
                     * (软删除) 删除房产成员信息登记表数据saas_community_house_user 删锁
                     */
                    // 房屋状态重置
                    House house2 = house.get();
                    house2.setMatchStatus(0);
                    house2.setLeaseStatus(0);
                    house2.setPaymentStatus(0);
                    house2.setRecordStatus(0);
                    house2.setPwdLockStatus(0);
                    house2.setCheckStatus(0);
                    house2.setUnitLesseeId(null);
                    houseRepository.save(house2);

                    // 删除成员新增人口流动记录
                    List<CommunityHouseUser> userList = communityHouseUserRepository.findByCommunityHouseIdAndStatus(houseId, 1);
                    for (CommunityHouseUser communityHouseUser : userList) {
                        if (communityHouseUser.getType() == 1 || communityHouseUser.getType() == 2) {
                            UserFloatingPopulationRecord record = new UserFloatingPopulationRecord();
                            record.setUserId(communityHouseUser.getUserId());
                            record.setUserPhone(communityHouseUser.getMobile());
                            record.setUserName(communityHouseUser.getName());
                            record.setHouseId(houseId);
                            record.setOrgId(house2.getOrgId());
                            record.setType(UserFloatingPopulationRecord.RECORD_TYPE_CHECK_OUT);
                            record.setCreateBy(tokenVo.getId());
                            record.setCreateTime(new Date());
                            userFloatingPopulationRecordService.insertUserFloatingPopulationRecord(record);
                        }
                    }


                    // 软删除房产所有成员信息
                    String deleteSql = "UPDATE saas_community_house_user SET status = 2 WHERE community_house_id = ?";
                    jdbcTemplate.update(deleteSql, houseId);

                    // 根据承租人id，房屋id，承租人类型来查找
                    List<MatchUserHouse> list = matchUserHouseRepository.findByHouseId(houseId);
                    if (list != null && !list.isEmpty()) {
                        String matchUserStockId = list.get(0).getMatchUserStockId();
                        if (StringUtils.isNotBlank(matchUserStockId)) {
                            // 承租人id、姓名、承租人与房屋关系id
                            String resultQuery = "SELECT mus.id,mus.match_user_id AS matchUserStockId,mus.name AS matchUserName"
                                    + " FROM m_match_user_stock mus \n" + " LEFT JOIN m_match_user_house muh \n"
                                    + " ON mus.id = muh.match_user_stock_id where muh.house_id not in ('" + houseId
                                    + "') and muh.match_user_stock_id = " + matchUserStockId + " and muh.label = 2 ";
                            List<MatchUserHouseVO> query = jdbcTemplate.query(resultQuery,
                                    new BeanPropertyRowMapper<>(MatchUserHouseVO.class));
                            // 承租人无其它房产信息时，才删除承租人库数据 （单位承租人类型）
                            if (query.isEmpty()) {
                                // 如果数据为空，则删除承租人库
                                communityHouseUserService.deleteMatch(accessToken, Long.valueOf(matchUserStockId), houseId, null);
                            }
                        }

                    }

                    if (lockId != null) {
                        // 清理房屋所有智能锁授权，包含智能锁授权记录
                        lockManagerService.deleteLockAuth(accessToken, lockId);
                    } else {
                        log.info("完成退租, 无智能锁绑定信息, houseId={}", houseId);
                    }

                    // 删除房屋与承租人关系数据
                    matchUserHouseRepository.deleteByHouseId(houseId);

                    // 变更余额状态 删除记录
                    House house1 = house.get();
                    List<Long> orgId = new ArrayList<>();
                    orgId.add(house1.getOrgId());
                    orgId.add(house1.getHousePropertyId());
                    List<HouseRemainingRelation> remRelationList = houseRemainingRelationRepository.findByOrgIdInAndHouseId(orgId, house1.getId());
                    if (!remRelationList.isEmpty()) {
                        List<Long> remId = remRelationList.stream().map(HouseRemainingRelation::getRemainingId).collect(Collectors.toList());
                        List<HouseRemaining> remainingList = houseRemainingRepository.findByIdIn(remId);
                        if (!remainingList.isEmpty()) {
                            remainingList.forEach(m -> {
                                m.setStatus(RemainingStatusEnum.RENUNCIATION.getValue());
                                m.setLastUpdateTime(new Date());

                                // 修复退租后延期记录未取消
                                List<HouseRemainingPostpone> postponeList = houseRemainingPostponeRepository.findByRemainingId(m.getId());
                                for (HouseRemainingPostpone houseRemainingPostpone : postponeList) {
                                    houseRemainingPostpone.setCancelStatus(1);
                                    HouseRemainingPostpone postpone = new HouseRemainingPostpone();
                                    postpone.setRemainingId(m.getId());
                                    postpone.setPostponeType(1);
                                    postpone.setOperatorId(redisService.getSystemUser());
                                    postpone.setLastUpdateTime(date);
                                    postpone.setCause("用户已退租");
                                    postpone.setDeferredStartTime(m.getDeferredStartTime());
                                    postpone.setDeferredEndTime(date);
                                    postpone.setPostponeId(houseRemainingPostpone.getId());
                                    houseRemainingPostponeRepository.save(postpone);
                                }
                                houseRemainingPostponeRepository.saveAll(postponeList);
                            });
                            houseRemainingRepository.saveAll(remainingList);
                        }
                        houseRemainingRelationRepository.deleteAll(remRelationList);
                    }
                    // 清除单位承租人账户
                    Long unitLesseeId = house1.getUnitLesseeId();
                    if (unitLesseeId != null) {
                        List<DepartmentRemainingRef> refs = departmentRemainingRefRepository.findByDepIdAndHouseId(unitLesseeId, houseId);
                        if (!refs.isEmpty()) {
                            departmentRemainingRefRepository.deleteAll(refs);
                        }
                    }

                }


                // 重置临时密码次数
                String updateMatchUserLock = "update m_match_user_lock_author set author_number=5,use_number=0,password_validity=1 WHERE house_id=?";
                jdbcTemplate.update(updateMatchUserLock, houseId);

                // 授权前更新未激活智能锁sn值
                if (lockId != null) {
                    Optional<SmartLock> lockOptional = smartLockRepository.findById(lockId);
                    if (lockOptional.isPresent()) {
                        SmartLock smartLock = lockOptional.get();
                        // 重置智能锁授权人数
                        lockAppService.updateOauthNumber(smartLock.getBrandId(), lockId);
                    } else {
                        log.info("完成退租, 重置锁人数失败, 锁未找到, houseId={}, lockId={}", houseId, lockId);
                    }
                }
                // 修改退租状态为：已完成
                houseSurrenderRepository.updateStatusfinsh(6, id, houseId);

                // 新增如果是合租房产下的子房产，退租后删除房产信息 20230307 xiaolong
                String housemateId = house.get().getHousemateId();
                if (housemateId != null) {
                    jdbcTemplate.update("update  saas_house set status = 2 WHERE id = ? ", houseId);
                    // 删除绑定待办事项
                    jdbcTemplate.update("DELETE from saas_backlog_item WHERE house_id = ? AND type = 1", houseId);

                    // 新增合租房子的最后一个房产退租完成后，爸爸房子也变成未出租状态 20230307 xiaolong
                    List<House> byHousemateIdAndStatus = houseRepository.findByHousemateIdAndStatus(housemateId, 1);
                    if (byHousemateIdAndStatus.size() == 0) {
                        List<House> byIdAndStatus = houseRepository.findByIdAndStatus(housemateId, 1);
                        if (!byIdAndStatus.isEmpty()) {
                            // 房屋状态重置
                            House house2 = byIdAndStatus.get(0);
                            house2.setMatchStatus(0);
                            house2.setLeaseStatus(0);
                            house2.setPaymentStatus(0);
                            house2.setRecordStatus(0);
                            house2.setPwdLockStatus(0);
                            house2.setCheckStatus(0);
                            houseRepository.save(house2);
                            // 根据承租人id，房屋id，承租人类型来查找
                            List<MatchUserHouse> list = matchUserHouseRepository.findByHouseId(housemateId);
                            if (list != null && !list.isEmpty()) {
                                String matchUserStockId = list.get(0).getMatchUserStockId();
                                if (StringUtils.isNotBlank(matchUserStockId)) {
                                    // 承租人id、姓名、承租人与房屋关系id
                                    String resultQuery = "SELECT mus.id,mus.match_user_id AS matchUserStockId,mus.name AS matchUserName"
                                            + " FROM m_match_user_stock mus \n" + " LEFT JOIN m_match_user_house muh \n"
                                            + " ON mus.id = muh.match_user_stock_id where muh.house_id not in ('" + housemateId
                                            + "') and muh.match_user_stock_id = " + matchUserStockId + " and muh.label = 2 ";
                                    List<MatchUserHouseVO> query = jdbcTemplate.query(resultQuery,
                                            new BeanPropertyRowMapper<>(MatchUserHouseVO.class));
                                    // 承租人无其它房产信息时，才删除承租人库数据 （单位承租人类型）
                                    if (query.isEmpty()) {
                                        // 如果数据为空，则删除承租人库
                                        communityHouseUserService.deleteMatch(accessToken, Long.valueOf(matchUserStockId), housemateId, null);
                                    }
                                }

                            }
                            // 房屋智能锁id
                            Long lockId1 = smartLockRepository.findByHouseIds(housemateId);
                            if (lockId1 != null) {
                                // 清理房屋所有智能锁授权，包含智能锁授权记录
                                lockManagerService.deleteLockAuth(accessToken, lockId1);
                            } else {
                                log.info("完成退租, 无智能锁绑定信息, houseId={}", housemateId);
                            }

                            // 删除房屋与承租人关系数据
                            matchUserHouseRepository.deleteByHouseId(housemateId);
                        }
                    }
                    //    新增删除智能锁与房屋信息
                    smartLockRepository.deleteByHouseId(houseId);
                }

                List<SmartLock> smartLock = smartLockRepository.findByHouseId(houseId);
                for (SmartLock lock : smartLock) {
                    lock.setMechineStatus(0);
                    lock.setReason(null);
                }
                smartLockRepository.saveAll(smartLock);

                List<EstateViolateBill> violateBills = estateViolateBillRepository.findByHouseIdAndLeaseStatus(houseId, 0);
                violateBills.forEach(m->m.setLeaseStatus(1));
                estateViolateBillRepository.saveAll(violateBills);

                // 删除人脸授权提示
                backlogItemRepository.deleteByHouseIdAndType(houseId, BacklogItemTypeEnum.FACE_AUTHORIZATION_REMINDER.getValue());

                // 删除账单生成规则
                houseExpenseRuleRefRepository.deleteByHouseId(houseId);

                return ResultBean.successfulResult("完成");
            } else {
                return ResultBean.failedResultOfParamWithMsg("房屋信息未找到");
            }

        } catch (Exception e) {
            log.error(e.getMessage(), e);
            e.printStackTrace();
            return ResultBean.failedResultOfException();
        }
    }


    /**
     * 短租公寓退租删除授权
     *
     * @param accessToken
     * @param houseId
     * @return
     */
    @Transactional(rollbackFor = {RuntimeException.class, Exception.class})
    public ResultBean<Object> deleteAuth(String accessToken, String houseId) {
        try {
            TokenRedisVo tokenVo = redisService.findTokenVo(accessToken);
            // 房屋智能锁id
            Long lockId = smartLockRepository.findByHouseIds(houseId);
            Optional<House> house = houseRepository.findById(houseId);
            if (house.isPresent()) {
                // 非公租房
                /*
                 * 需要删除房屋与承租人关系m_match_user_house，如果该承租人没有与其它房屋关联记录，承租人也需要删除
                 * (软删除) 删除房产成员信息登记表数据saas_community_house_user 删锁
                 */
                // 房屋状态重置
                House house2 = house.get();
                house2.setMatchStatus(0);
                house2.setLeaseStatus(0);
                house2.setPaymentStatus(0);
                house2.setRecordStatus(0);
                house2.setPwdLockStatus(0);
                house2.setCheckStatus(0);
                house2.setUnitLesseeId(null);
                houseRepository.save(house2);

                // 删除成员新增人口流动记录
                List<CommunityHouseUser> userList = communityHouseUserRepository.findByCommunityHouseIdAndStatus(houseId, 1);
                for (CommunityHouseUser communityHouseUser : userList) {
                    if (communityHouseUser.getType() == 1 || communityHouseUser.getType() == 2) {
                        UserFloatingPopulationRecord record = new UserFloatingPopulationRecord();
                        record.setUserId(communityHouseUser.getUserId());
                        record.setUserPhone(communityHouseUser.getMobile());
                        record.setUserName(communityHouseUser.getName());
                        record.setHouseId(houseId);
                        record.setOrgId(house2.getOrgId());
                        record.setType(UserFloatingPopulationRecord.RECORD_TYPE_CHECK_OUT);
                        record.setCreateBy(tokenVo.getId());
                        record.setCreateTime(new Date());
                        userFloatingPopulationRecordService.insertUserFloatingPopulationRecord(record);
                    }
                }
                // 软删除房产所有成员信息
                String deleteSql = "UPDATE saas_community_house_user SET status = 2 WHERE community_house_id = ?";
                jdbcTemplate.update(deleteSql, houseId);

                // 根据承租人id，房屋id，承租人类型来查找
                List<MatchUserHouse> list = matchUserHouseRepository.findByHouseId(houseId);
                if (list != null && !list.isEmpty()) {
                    String matchUserStockId = list.get(0).getMatchUserStockId();
                    if (StringUtils.isNotBlank(matchUserStockId)) {
                        // 承租人id、姓名、承租人与房屋关系id
                        String resultQuery = "SELECT mus.id,mus.match_user_id AS matchUserStockId,mus.name AS matchUserName"
                                + " FROM m_match_user_stock mus \n" + " LEFT JOIN m_match_user_house muh \n"
                                + " ON mus.id = muh.match_user_stock_id where muh.house_id not in ('" + houseId
                                + "') and muh.match_user_stock_id = " + matchUserStockId + " and muh.label = 2 ";
                        List<MatchUserHouseVO> query = jdbcTemplate.query(resultQuery,
                                new BeanPropertyRowMapper<>(MatchUserHouseVO.class));
                        // 承租人无其它房产信息时，才删除承租人库数据 （单位承租人类型）
                        if (query.isEmpty()) {
                            // 如果数据为空，则删除承租人库
                            communityHouseUserService.deleteMatch(accessToken, Long.valueOf(matchUserStockId), houseId, null);
                        }
                    }

                }

                if (lockId != null) {
                    // 清理房屋所有智能锁授权，包含智能锁授权记录
                    lockManagerService.deleteLockAuth(accessToken, lockId);
                } else {
                    log.info("完成退租, 无智能锁绑定信息, houseId={}", houseId);
                }

                // 删除房屋与承租人关系数据
                matchUserHouseRepository.deleteByHouseId(houseId);

                // 重置临时密码次数
                String updateMatchUserLock = "update m_match_user_lock_author set author_number=5,use_number=0,password_validity=1 WHERE house_id=?";
                jdbcTemplate.update(updateMatchUserLock, houseId);

                // 授权前更新未激活智能锁sn值
                if (lockId != null) {
                    Optional<SmartLock> lockOptional = smartLockRepository.findById(lockId);
                    if (lockOptional.isPresent()) {
                        SmartLock smartLock = lockOptional.get();
                        // 重置智能锁授权人数
                        lockAppService.updateOauthNumber(smartLock.getBrandId(), lockId);
                    } else {
                        log.info("完成退租, 重置锁人数失败, 锁未找到, houseId={}, lockId={}", houseId, lockId);
                    }
                }
                // 新增如果是合租房产下的子房产，退租后删除房产信息 20230307 xiaolong
                String housemateId = house.get().getHousemateId();
                if (housemateId != null) {
                    jdbcTemplate.update("update  saas_house set status = 2 WHERE id = ? ", houseId);
                    // 删除绑定待办事项
                    jdbcTemplate.update("DELETE from saas_backlog_item WHERE house_id = ? AND type = 1", houseId);

                    // 新增合租房子的最后一个房产退租完成后，爸爸房子也变成未出租状态 20230307 xiaolong
                    List<House> byHousemateIdAndStatus = houseRepository.findByHousemateIdAndStatus(housemateId, 1);
                    if (byHousemateIdAndStatus.size() == 0) {
                        List<House> byIdAndStatus = houseRepository.findByIdAndStatus(housemateId, 1);
                        if (!byIdAndStatus.isEmpty()) {
                            // 房屋状态重置
                            House house1 = byIdAndStatus.get(0);
                            house1.setMatchStatus(0);
                            house1.setLeaseStatus(0);
                            house1.setPaymentStatus(0);
                            house1.setRecordStatus(0);
                            house1.setPwdLockStatus(0);
                            house1.setCheckStatus(0);
                            houseRepository.save(house1);
                            // 根据承租人id，房屋id，承租人类型来查找
                            List<MatchUserHouse> byHouseId = matchUserHouseRepository.findByHouseId(housemateId);
                            if (byHouseId != null && !byHouseId.isEmpty()) {
                                String matchUserStockId = byHouseId.get(0).getMatchUserStockId();
                                if (StringUtils.isNotBlank(matchUserStockId)) {
                                    // 承租人id、姓名、承租人与房屋关系id
                                    String resultQuery = "SELECT mus.id,mus.match_user_id AS matchUserStockId,mus.name AS matchUserName"
                                            + " FROM m_match_user_stock mus \n" + " LEFT JOIN m_match_user_house muh \n"
                                            + " ON mus.id = muh.match_user_stock_id where muh.house_id not in ('" + housemateId
                                            + "') and muh.match_user_stock_id = " + matchUserStockId + " and muh.label = 2 ";
                                    List<MatchUserHouseVO> query = jdbcTemplate.query(resultQuery,
                                            new BeanPropertyRowMapper<>(MatchUserHouseVO.class));
                                    // 承租人无其它房产信息时，才删除承租人库数据 （单位承租人类型）
                                    if (query.isEmpty()) {
                                        // 如果数据为空，则删除承租人库
                                        communityHouseUserService.deleteMatch(accessToken, Long.valueOf(matchUserStockId), housemateId, null);
                                    }
                                }

                            }
                            // 房屋智能锁id
                            Long lockId1 = smartLockRepository.findByHouseIds(housemateId);
                            if (lockId1 != null) {
                                // 清理房屋所有智能锁授权，包含智能锁授权记录
                                lockManagerService.deleteLockAuth(accessToken, lockId1);
                            } else {
                                log.info("完成退租, 无智能锁绑定信息, houseId={}", housemateId);
                            }

                            // 删除房屋与承租人关系数据
                            matchUserHouseRepository.deleteByHouseId(housemateId);
                        }
                    }
                    //    新增删除智能锁与房屋信息
                    smartLockRepository.deleteByHouseId(houseId);
                }

                return ResultBean.successfulResult("完成");
            } else {
                return ResultBean.failedResultOfParamWithMsg("房屋信息未找到");
            }

        } catch (Exception e) {
            log.error(e.getMessage(), e);
            e.printStackTrace();
            return ResultBean.failedResultOfException();
        }
    }

    /**
     * 导出退租账单
     *
     * @param billId   账单id
     * @param response
     * @throws UnsupportedEncodingException
     * @throws FileNotFoundException        找到对应的账单并根据账单模板生成账单
     */
    public void loadBill(Long billId, HttpServletResponse response)
            throws UnsupportedEncodingException, FileNotFoundException {
        try {
            // 根据退租id，查到是公租房还是非公租房
            // 根据账单id找到退租id
            String sqlHouseSurrend = "SELECT mhs.* FROM m_house_surrender mhs\n"
                    + "INNER JOIN saas_expense_bill seb  \n" + "ON seb.id = mhs.bill_id\n" + "WHERE seb.id = ?\n"
                    + "UNION ALL\n" + "SELECT mhs.* FROM m_house_surrender mhs \n"
                    + "INNER JOIN  saas_estate_expense_bill seeb\n" + "ON seeb.id = mhs.bill_id\n"
                    + "WHERE seeb.id = ?\n";

            List<HouseSurrenderVO> withdrawId = jdbcTemplate.query(sqlHouseSurrend,
                    new BeanPropertyRowMapper<>(HouseSurrenderVO.class), billId, billId);
            if (withdrawId == null || withdrawId.isEmpty()) {
                log.error("退租账单未找到, billId={}", billId);
                throw new CustomException("账单数据有误");
            }
            Optional<HouseSurrender> byId = houseSurrenderRepository.findById(withdrawId.get(0).getId());
            if (byId.isPresent()) {
                HouseSurrender ho = byId.get();
                if (ho.getStatus() == 0 || ho.getStatus() == 1 || ho.getStatus() == 2) {
                    log.error("退租资料验证有误, 无法下载, houseSurrenderId={}", ho.getId());
                    throw new CustomException("退租资料验证有误");
                }
                Optional<House> byId1 = houseRepository.findById(ho.getHouseId());
                if (byId1.isPresent()) {
                    List<LoadBillVO> loadBillVOS;
                    Integer houseNature = byId1.get().getHouseNature();

                    if (houseNature == 1) {
                        // 公租房
                        String querySql = "SELECT sh.id AS houseId,seb.id AS id,sc.name AS communityName,b.name AS buildingName,u.name AS unitName,d.name AS orgName,sh.room_number AS roomNumber,shc.match_user_id AS matchUserId,shc.start_time AS startTime,shc.ent_time AS afterTime,sh.area AS billingArea FROM saas_expense_bill seb\n"
                                + "LEFT JOIN saas_expense_bill_relation sebr\n" + "ON seb.id = sebr.expense_bill_id\n"
                                + "LEFT JOIN saas_house sh\n" + "ON sebr.house_id = sh.id\n"
                                + "LEFT JOIN saas_house_contract shc\n" + "ON shc.house_id = sh.id\n"
                                + "LEFT JOIN saas_house_match_user shmu\n" + "ON shmu.house_id = sh.id\n"
                                + "LEFT JOIN saas_house_eligible_apply shea\n" + "ON shmu.apply_id = shea.id\n"
                                + "LEFT JOIN saas_community sc\n" + "ON sc.id = sh.residence_community_id\n"
                                + "LEFT JOIN saas_community_build b\n" + "ON sh.building_num = b.id\n"
                                + "LEFT JOIN saas_community_build_unit u\n" + "ON sh.unit = u.id\n"
                                + "LEFT JOIN saas_department d \n" + "ON sh.org_id = d.id\n" + "WHERE seb.id = ?";
                        loadBillVOS = jdbcTemplate.query(querySql, new BeanPropertyRowMapper<>(LoadBillVO.class),
                                billId);
                        Optional<ExpenseBill> byId2 = expenseBillRepository.findById(billId);
                        if (byId2.isPresent()) {
                            ExpenseBill expenseBill = byId2.get();
                            if (expenseBill.getOrgId() != null) {
                                Optional<Department> departmentOptional = departmentRepository
                                        .findById(expenseBill.getOrgId());
                                if (departmentOptional.isPresent()) {
                                    loadBillVOS.forEach(i -> {
                                        i.setOrgName(departmentOptional.get().getName());
                                    });
                                }
                            }
                        }
                    } else {
                        // 非公租房
                        String querySql = "SELECT sh.id AS houseId,seeb.id AS id,sc.name AS communityName,b.name AS buildingName,u.name AS unitName,"
                                + "d.name AS orgName,sh.room_number AS roomNumber,seeb.match_user_id AS matchUserId,sur.start_time AS startTime,"
                                + "sur.end_time AS afterTime,sh.area AS billingArea FROM saas_estate_expense_bill seeb\n"
                                + "LEFT JOIN saas_house sh\n" + "ON seeb.house_id = sh.id\n"
                                + "LEFT JOIN m_house_surrender sur ON sur.house_id = sh.id \n"
                                + "LEFT JOIN saas_community sc\n" + "ON sc.id = sh.residence_community_id\n"
                                + "LEFT JOIN saas_community_build b\n" + "ON sh.building_num = b.id\n"
                                + "LEFT JOIN saas_community_build_unit u\n" + "ON sh.unit = u.id\n"
                                + "LEFT JOIN saas_department d \n" + "ON sh.org_id = d.id\n" + "WHERE seeb.id = ?";
                        loadBillVOS = jdbcTemplate.query(querySql, new BeanPropertyRowMapper<>(LoadBillVO.class),
                                billId);
                        // 根据账单id查询账单表，再根据orgId(orgId可能为空)，取得orgName
                        Optional<EstateExpenseBill> byId2 = estateExpenseBillRepository.findById(billId);
                        if (byId2.isPresent()) {
                            EstateExpenseBill estateExpenseBill = byId2.get();
                            if (estateExpenseBill.getOrgId() != null) {
                                Optional<Department> departmentOptional = departmentRepository
                                        .findById(estateExpenseBill.getOrgId());
                                if (departmentOptional.isPresent()) {
                                    loadBillVOS.forEach(i -> {
                                        i.setOrgName(departmentOptional.get().getName());
                                    });
                                }
                            }
                        }
                    }

                    LoadBillVO loadBillVO = loadBillVOS.get(0);

                    ExcelTemplateVo excelTemplateVo = new ExcelTemplateVo();
                    excelTemplateVo.setCommunityName(loadBillVO.getCommunityName());
                    excelTemplateVo.setBuildingName(loadBillVO.getBuildingName());
                    excelTemplateVo.setUnitName(loadBillVO.getUnitName());
                    String startTime = loadBillVO.getStartTime() != null ? new SimpleDateFormat("yyyy年MM月dd日").format(loadBillVO.getStartTime()) : null;
                    String endTime = loadBillVO.getAfterTime() != null ? new SimpleDateFormat("yyyy年MM月dd日").format(loadBillVO.getAfterTime()) : null;
                    excelTemplateVo.setStartTime(startTime);
                    excelTemplateVo.setAfterTime(endTime);
                    excelTemplateVo.setRoomNumber(loadBillVO.getRoomNumber() != null ? loadBillVO.getRoomNumber().toString() : "");
                    excelTemplateVo.setBillingArea(loadBillVO.getBillingArea());
                    excelTemplateVo.setRentTotalPrice(withdrawId.get(0).getRentAmount());
                    excelTemplateVo.setDepositTotalPrice(withdrawId.get(0).getCashPledgeAmount());
                    excelTemplateVo.setOtherTotalPrice(withdrawId.get(0).getOtherAmount());
                    excelTemplateVo.setPayable(String.valueOf(withdrawId.get(0).getSettlementAmount()));
                    excelTemplateVo.setOrgName(loadBillVO.getOrgName());
                    excelService.exportTemplateExcel(excelTemplateVo, response);
                }
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    public File file(Workbook workbook, String fileName) throws IOException {

        // 生成文件
        File file = new File(contractTmpDir + fileName + ".xlsx");
        OutputStream out = new BufferedOutputStream(new FileOutputStream(file));
        try {
            if (!file.exists()) {
                file.createNewFile();
            }
            workbook.write(out);

        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            if (out != null) {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

        return file;

    }

}
